package com.nit.trash.mapper;

import com.nit.trash.dto.UserDTO;
import com.nit.trash.dto.MenuDTO;
import com.nit.trash.entity.UserEntity;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface UserMapper {
    @Select("SELECT *, user_id AS `key` from user ")
    List<UserDTO> listAll() throws Exception;

    /**
     * 查询用户总数
     * @return List
     */
    @Select({"<script>SELECT COUNT(*) FROM user " +
            "WHERE role_id = 2 " +
            "<if test = 'userName != null'>AND user_name LIKE CONCAT('%', #{userName}, '%') </if>" +
            "<if test = 'userType != null'>AND user_type = #{userType} </if>" +
            "<if test = 'phone != null'>AND phone LIKE CONCAT('%', #{phone}, '%') </if>" +
            "</script>"})
    Long count4Table(@Param("userName")String userName,
                     @Param("userType")String userType,
                     @Param("phone")String phone) throws Exception;

    /**
     * 查询用户信息封装便于前端展示
     * @return List
     */
    @Select({"<script>SELECT user_id, user_name, phone, id_number, user_type, gmt_create, " +
            "user_id AS `key` FROM user " +
            "WHERE role_id = 2 " +
            "<if test = 'userName != null'>AND user_name LIKE CONCAT('%', #{userName}, '%') </if>" +
            "<if test = 'userType != null'>AND user_type = #{userType} </if>" +
            "<if test = 'phone != null'>AND phone LIKE CONCAT('%', #{phone}, '%') </if>" +
            "ORDER BY " +
            "<if test = 'sortField != null'>${sortField} ${sortOrder}, </if>" +
            "user_id DESC " +
            "LIMIT #{start}, #{length}" +
            "</script>"})
    List<UserDTO> list4Table(
            @Param("userName")String userName,
            @Param("userType")String userType,
            @Param("phone")String phone,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField")String sortField,
            @Param("sortOrder")String sortOrder) throws Exception;

    /**
     * 查询用户统计信息
     * @return OpResult
     */
    @Select({"<script>SELECT user_id, user_name, phone, gmt_create, total, u.right, wrong, " +
            "(case when total = 0 then 0 when total > 0 then u.right / total end) AS `accuracy`, " +
            "(total - u.right - wrong) AS `noAnswer`, " +
            "user_id AS `key` FROM user u " +
            "WHERE TRUE " +
            "<if test = 'userName != null'>AND user_name LIKE CONCAT('%', #{userName}, '%') </if>" +
            "<if test = 'phone != null'>AND phone LIKE CONCAT('%', #{phone}, '%') </if>" +
            "ORDER BY " +
            "<if test = 'sortField != null'>${sortField} ${sortOrder}, </if>" +
            "total DESC, u.right DESC, user_id " +
            "LIMIT #{start}, #{length}" +
            "</script>"})
    List<UserDTO> statistics(
            @Param("userName")String userName,
            @Param("phone")String phone,
            @Param("start") Integer start,
            @Param("length") Integer length,
            @Param("sortField")String sortField,
            @Param("sortOrder")String sortOrder) throws Exception;

    /**
     * 查询用户统计信息
     * @return OpResult
     */
    @Select({"<script>SELECT count(*) from user " +
            "WHERE TRUE " +
            "<if test = 'userName != null'>AND user_name LIKE CONCAT('%', #{userName}, '%') </if>" +
            "<if test = 'phone != null'>AND phone LIKE CONCAT('%', #{phone}, '%') </if>" +
            "</script>"})
    Long countStatistics(
            @Param("userName")String userName,
            @Param("phone")String phone) throws Exception;

    @Insert("INSERT INTO user " +
            "(user_name, role_id, phone, password, id_number, user_type, total, user.right, wrong, gmt_create) " +
            "VALUES(#{entity.userName}, #{entity.roleId}, #{entity.phone}, #{entity.password}, " +
            "#{entity.idNumber}, #{entity.userType}, #{entity.total}, #{entity.right}, " +
            "#{entity.wrong}, #{entity.gmtCreate})")
    @Options(useGeneratedKeys = true, keyProperty = "userId", keyColumn = "user_id")
    Integer add(@Param("entity") UserEntity entity) throws Exception;

    /**
     * @Description: 根据用户名获取用户信息
     * @Date: 2020/12/22
     */
    @Select({"SELECT *, user_id AS `key` " +
            "FROM user " +
            "WHERE user_name = #{userName} LIMIT 1"
    })
    UserDTO getByUserName(@Param("userName") String userName) throws Exception;

    /**
     * @Description: 根据用户ID获取用户信息
     * @Date: 2020/12/22
     */
    @Select({"SELECT *, user_id AS `key` " +
            "FROM user " +
            "WHERE user_id = #{userId} LIMIT 1 "
    })
    UserDTO getByUserId(@Param("userId") Long userId) throws Exception;

    /**
     * @Description: 更新用户状态
     * @Date: 2020/12/26
     */
    @Update("UPDATE user " +
            "SET user_type = #{userType} " +
            "WHERE user_id = #{userId} LIMIT 1")
    Integer updateUserType(
            @Param("userId")Long userId,
            @Param("userType")Integer userType) throws Exception;

    /**
     * 查询用户权限树
     *
     * @param roleId
     * @return
     * @throws Exception
     */
    @Select("SELECT a.* FROM menu a JOIN role_menu b " +
            "ON a.menu_id = b.menu_id AND b.role_id = #{roleId} " +
            "ORDER BY a.father_id, a.menu_sort ")
    List<MenuDTO> queryRoleMenu(@Param("roleId") Integer roleId) throws Exception;

    /**
     * @Description: 更新统计数据
     * @Date: 2020/12/29
     */
    @Update("UPDATE user u " +
            "SET u.total = u.total + #{entity.total}, u.right = u.right + #{entity.right}, " +
            "u.wrong = u.wrong + #{entity.wrong} " +
            "WHERE user_id = #{entity.userId} LIMIT 1")
    void updateData(
            @Param("entity")UserEntity userEntity) throws Exception;
}
